
package com.scalar.chiptrack.operations.process.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.forms.AssemblyForm;
import com.scalar.chiptrack.utils.SqlQueryMappings;


public class AssemblyProcessDAO {

    
    public static AssemblyForm loadAssemblyProcessData (int processStepId) throws ScalarException {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        
        AssemblyForm assemblyData = null;
        
        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.assembly.loadAssemblyData"));
            pstmt.setInt (1, processStepId);
            
            rset = pstmt.executeQuery();

            if (rset.next()) {
                assemblyData = new AssemblyForm (rset.getString ("AT_Package_ID"),
                                                  rset.getFloat  ("AT_Standard_Unit_Cost"),
                                                  rset.getFloat  ("AT_Target_Yield"),
                                                  rset.getFloat  ("AT_Lot_Scrap_Yield_Limit"),
                                                  rset.getString ("AT_PO_Frequency"),
                                                  rset.getString ("Procedure_Doc_Number"),
                                                  rset.getString ("PDT_File_Name"),
                                                  rset.getString ("BDDT_Doc_Number"),
                                                  rset.getString ("BDDT_File_Name"),
                                                  rset.getString ("MST_Number"),
                                                  rset.getString ("MST_File_Name"),
                                                  ((rset.getInt ("AT_Datecode_Marked") == 0) ? "on" : "off"),
                                                  rset.getString ("Part_Number"), 
                                                  rset.getInt    ("P_Parent_Product_ID"), 
                                                  rset.getString ("PS_Type"), 
                                                  rset.getString ("PS_Comments"),
                                                  rset.getInt    ("previousStep_ID"));
            }
            if( assemblyData != null )
            {
				assemblyData.setDupProductName(ProductDao.loadProductDupNames( processStepId ));
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Assembly Process Data from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return assemblyData;
    }
    
    
    public static void storeAssemblyProcessData (DBTransaction dbTransaction,
                                                      int processStepId,
                                                      String packageId,
                                                      double standardUnitCost,
                                                      double targetYield,
                                                      double lotScrapYieldLimit,
                                                      String poFrequency,
                                                      int procedureDocId,
                                                      int bondingDocId,
                                                      int markingSpecId,
                                                      String dataCodeMarked) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("opearations.assembly.insertNewRow"));

            pstmt.setInt (1, processStepId);
            pstmt.setString (2, packageId);
            pstmt.setDouble (3, standardUnitCost);
            pstmt.setDouble (4, targetYield);
            pstmt.setDouble (5, lotScrapYieldLimit);
            pstmt.setString (6, poFrequency);
            pstmt.setInt (7, procedureDocId);
            pstmt.setInt (8, bondingDocId);
            pstmt.setInt (9, markingSpecId);
            pstmt.setInt (10, ((dataCodeMarked.trim().length() == 0) ? 1 : 0));
            
            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert a row into Assembly table (preocess stepID = " + processStepId + ")", sqle);
        }
        finally {
            
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }   
    }

	 //Method Update Assembly Data by Raj Vallamsetla

	 public static void updateAssemblyProcessData (DBTransaction dbTransaction,
                                                      int processStepId,
                                                      String packageId,
                                                      double standardUnitCost,
                                                      double targetYield,
                                                      double lotScrapYieldLimit,
                                                      String poFrequency,
                                                      int procedureDocId,
                                                      int bondingDocId,
                                                      int markingSpecId,
                                                      String dataCodeMarked) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("opearations.assembly.update"));

            pstmt.setString (1, packageId);
            pstmt.setDouble (2, standardUnitCost);
            pstmt.setDouble (3, targetYield);
            pstmt.setDouble (4, lotScrapYieldLimit);
            pstmt.setString (5, poFrequency);
            pstmt.setInt (6, procedureDocId);
            pstmt.setInt (7, bondingDocId);
            pstmt.setInt (8, markingSpecId);
            pstmt.setInt (9, ((dataCodeMarked.trim().length() == 0) ? 1 : 0));
			pstmt.setInt (10, processStepId);
            
            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Update a row into Assembly table (preocess stepID = " + processStepId + ")", sqle);
        }
        finally {
            
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }   
    }


    /**
     * @param string
     * @param i
     */
    public static String checkProductNameInTrackTable(String productName, int processStepId) throws ScalarException
    {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		String result = "";
        
		try 
		{
			conn = DataSource.getConnection();

			pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.assembly.checkAssemblyPartNumbers"));
			pstmt.setString(1, productName);
			pstmt.setInt (2, processStepId);
            
			rset = pstmt.executeQuery();

			if (rset.next()) 
			{
				result = rset.getString("Product_Name");
			}
			
		}
		catch (Exception sqle) {
			sqle.printStackTrace();
			throw new ScalarException ("Could not Check for Dulicates in Assembly Track Table", sqle);
		}
		finally 
		{
			if (rset != null) {
				try { rset.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
    	
    	
        return result;
    }


}
